﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Basic4Net.Entities;
using System.Data.Common;
using System.Data.Objects;
using System.Collections;

namespace Basic4Net.BusinessRules
{
	public class ReportYearBR : BaseBR
	{
		/// <summary>
		/// 获取 年度教职工绩效工资发放统计表
		/// </summary>
		/// <param name="Year">年度</param>
		/// <param name="TeachType">教师类型：1在职，2临聘</param>
		/// <param name="PayAmount">年度总额</param>
		/// <param name="AppAmount">发放总额</param>
		/// <returns>员工年度数据列表</returns>
		public List<DbDataRecord> GetList(bool IsAdmin , int UserID ,int Year, int TeachType, ref double? PayAmount, ref double? AppAmount)
		{
			using (DBEntities db = new DBEntities())
			{
				string esql = String.Format("select t.TeacherID as UserID,u.TEACHNAME as UserName,u.TEACHCODE as UserCode, max(CASE when t.FMonth == 1 then t.AmountActual else null end) as Money1, max(CASE when t.FMonth == 2 then t.AmountActual else null end) as Money2, max(CASE when t.FMonth == 3 then t.AmountActual else null end) as Money3, max(CASE when t.FMonth == 4 then t.AmountActual else null end) as Money4, max(CASE when t.FMonth == 5 then t.AmountActual else null end) as Money5, max(CASE when t.FMonth == 6 then t.AmountActual else null end) as Money6, max(CASE when t.FMonth == 7 then t.AmountActual else null end) as Money7, max(CASE when t.FMonth == 8 then t.AmountActual else null end) as Money8, max(CASE when t.FMonth == 9 then t.AmountActual else null end) as Money9, max(CASE when t.FMonth == 10 then t.AmountActual else null end) as Money10, max(CASE when t.FMonth == 11 then t.AmountActual else null end) as Money11, max(CASE when t.FMonth == 12 then t.AmountActual else null end) as Money12, 0 as Money13, sum(t.AmountActual) as MoneyTotal from DBEntities.t_MeritPay as t left join DBEntities.TEACHERINFO as u on t.TeacherID = u.RID where t.TeachType = {0} and t.FYear = {1} group by t.TeacherID,u.TEACHNAME,u.TEACHCODE",
					TeachType, Year);
				ObjectQuery<DbDataRecord> query = db.CreateQuery<DbDataRecord>(esql);

				var Total = (from e in db.t_Amount
							 where e.FYear == Year && e.AppType == TeachType
							 group e by e.FYear into temp
							 select new
							 {
								 PayAmount = temp.Sum(t => t.PayAmount),
								 AppAmount = temp.Sum(t => t.AppAmount)
							 }).FirstOrDefault();

				PayAmount = Total == null ? (double?)null : Total.PayAmount;
				AppAmount = Total == null ? (double?)null : Total.AppAmount;
				return (from q in query.ToList()
					   where IsAdmin ? true : (int)q["UserID"] == UserID
					   select q).ToList();
			}
		}

		/// <summary>
		/// 寒暑假教职工岗位工作量统计表
		/// </summary>
		/// <param name="SchoolYear">年份(学年)</param>
		/// <param name="Month">月份</param>
		/// <returns>工作量列表</returns>
		public List<HolidayWorkReportShow> GetHolidayWorkList(bool IsAdmin, int UserID,int SchoolYear, int Month)
		{
			using (DBEntities db = new DBEntities())
			{
				var query = from m in db.t_MeritPay
							join a in db.t_Amount on new { m.FYear, m.FMonth } equals new { a.FYear, a.FMonth }
							join u in db.TEACHERINFO on m.TeacherID equals u.RID
							where a.HolType == 1 && a.SchoolYear == SchoolYear && a.FMonth == Month && a.AppType == 1
							&& (IsAdmin ? true : u.RID == UserID)
							orderby u.TEACHCODE ascending
							select new HolidayWorkReportShow
							{
								ID = u.RID,
								Code = u.TEACHCODE,
								Name = u.TEACHNAME,
								Value = m.HolidayWork ?? 0,
								Remark = m.HolidayWorkContent
							};
				return query.ToList();
			}
		}

		/// <summary>
		/// 获取寒暑假月份列表
		/// </summary>
		/// <returns>用于显示</returns>
		public object GetHolidayList()
		{
			using (DBEntities db = new DBEntities())
			{
				var query = from a in db.t_Amount
							where a.HolType == 1 && a.AppType == 1
							orderby a.FYear descending, a.FMonth descending
							select new
							{
								Year = a.FYear,
								Month = a.FMonth,
								Term = a.Term,
								SchoolYear = a.SchoolYear
							};
				ArrayList list = new ArrayList();
				foreach (var a in query.Take(5).ToList())
				{
					list.Add(new
					{
						String = ((a.Term ?? 1) == 1 ? (a.SchoolYear + "年度寒假（" + a.Month + "月)") : (a.SchoolYear + "年度暑假（" + a.Month + "月)")),
						Value = a.SchoolYear + "-" + a.Month
					});
				}
				return list;
			}
		}

		/// <summary>
		/// 获取学期教职工奖教奖学评比结果
		/// </summary>
		/// <param name="SchoolYear">学年</param>
		/// <param name="Term">学期(1上学期，2下学期)</param>
		/// <returns>学期教职工奖教奖学评比结果</returns>
		public List<WithholdMoneyReportShow> GetWithholdMoneyList(bool IsAdmin, int UserID,int SchoolYear, int Term)
		{
			using (DBEntities db = new DBEntities())
			{
				var query = from m in db.t_MeritPay
							join a in db.t_Amount on new { m.FYear, m.FMonth } equals new { a.FYear, a.FMonth }
							join u in db.TEACHERINFO on m.TeacherID equals u.RID
							where a.Term == Term && a.SchoolYear == SchoolYear && a.AppType == 1
							&& (IsAdmin ? true : u.RID == UserID)
							group m by new { u.RID, u.TEACHCODE, u.TEACHNAME } into tempm
							orderby tempm.Key.TEACHCODE ascending
							select new WithholdMoneyReportShow
							{
								ID = tempm.Key.RID,
								Code = tempm.Key.TEACHCODE,
								Name = tempm.Key.TEACHNAME,
								ActualWorkload = tempm.Sum(t => t.ActualWorkload) ?? 0,
								WithholdMoney = tempm.Sum(t => t.WithholdMoney) ?? 0
							};
				return query.ToList();
			}
		}

		/// <summary>
		/// 获取用于显示的下拉框学年学期
		/// </summary>
		/// <returns>用于显示ddl</returns>
		public object GetWithholdMoneyYearTermList()
		{
			using (DBEntities db = new DBEntities())
			{
				var query = from a in db.t_Amount
							where a.AppType == 1
							orderby a.SchoolYear descending, a.Term ascending
							select new
							{
								SchoolYear = a.SchoolYear,
								Term = a.Term
							};
				ArrayList list = new ArrayList();
				foreach (var a in query.Take(5).ToList())
				{
					list.Add(new
					{
						String = ((a.Term ?? 1) == 1 ? (a.SchoolYear + "年度上学期") : (a.SchoolYear + "年度下学期")),
						Value = a.SchoolYear + "-" + a.Term
					});
				}
				return list;
			}
		}
	}

	public sealed class HolidayWorkReportShow
	{
		public int ID { get; set; }
		public String Name { get; set; }
		public String Code { get; set; }
		public double Value { get; set; }
		public String Remark { get; set; }
	}
	public sealed class WithholdMoneyReportShow
	{
		public int ID { get; set; }
		public String Name { get; set; }
		public String Code { get; set; }
		public double ActualWorkload { get; set; }
		public double WithholdMoney { get; set; }
	}
	public sealed class WholeYearFinanceReportShow
	{
		public int UserID { get; set; }
		public String UserName { get; set; }
		public String UserCode { get; set; }
		public double Money1 { get; set; }
		public double Money2 { get; set; }
		public double Money3 { get; set; }
		public double Money4 { get; set; }
		public double Money5 { get; set; }
		public double Money6 { get; set; }
		public double Money7 { get; set; }
		public double Money8 { get; set; }
		public double Money9 { get; set; }
		public double Money10 { get; set; }
		public double Money11 { get; set; }
		public double Money12 { get; set; }
		public double Money13 { get; set; }
		public double MoneyTotal { get; set; }
	}
}
